﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_StockOutGoodsInfoDal : SqlServerHelper, IDisposable
    {
        public bool AddG_StockOutGoodsInfo(G_StockOutGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_StockOutGoodsInfo");
            strSql.Append("(");
            strSql.Append("StockOutId,");
            strSql.Append("GoodsId,");
            strSql.Append("GoodsName,");
            strSql.Append("Num,");
            strSql.Append("UnitValue");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@StockOutId,");
            strSql.Append("@GoodsId,");
            strSql.Append("@GoodsName,");
            strSql.Append("@Num,");
            strSql.Append("@UnitValue");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutId", SqlDbType.Int, 4) { Value = model.StockOutId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 50) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@Num", SqlDbType.Decimal, 9) { Value = model.Num });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_StockOutGoodsInfo(int StockOutGoodsId)
        {
            string strSql = "DELETE FROM G_StockOutGoodsInfo WHERE StockOutGoodsId=@StockOutGoodsId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutGoodsId", SqlDbType.Int, 4) { Value = StockOutGoodsId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool EditG_StockOutGoodsInfo(G_StockOutGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockOutGoodsInfo SET ");
            strSql.Append(" StockOutId=@StockOutId,");
            strSql.Append(" GoodsId=@GoodsId,");
            strSql.Append(" GoodsName=@GoodsName,");
            strSql.Append(" Num=@Num,");
            strSql.Append(" UnitValue=@UnitValue");
            strSql.Append(" WHERE StockOutGoodsId=@StockOutGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutGoodsId", SqlDbType.Int, 4) { Value = model.StockOutGoodsId });
            pList.Add(new SqlParameter("@StockOutId", SqlDbType.Int, 4) { Value = model.StockOutId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 50) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@Num", SqlDbType.Decimal, 9) { Value = model.Num });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<G_StockOutGoodsInfo> GetG_StockOutGoodsInfoList(string strWhere)
        {
            List<G_StockOutGoodsInfo> list = new List<G_StockOutGoodsInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" StockOutGoodsId");
            strSql.Append(",StockOutId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",Num");
            strSql.Append(",UnitValue");
            strSql.AppendFormat(" FROM G_StockOutGoodsInfo WITH(NOLOCK) WHERE 1=1{0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockOutGoodsId = r.GetOrdinal("StockOutGoodsId");
                int StockOutId = r.GetOrdinal("StockOutId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int Num = r.GetOrdinal("Num");
                int UnitValue = r.GetOrdinal("UnitValue");
                G_StockOutGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockOutGoodsInfo();
                    model.StockOutGoodsId = r.GetInt32(StockOutGoodsId);
                    model.StockOutId = r.GetInt32(StockOutId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.Num = r.GetDecimal(Num);
                    model.UnitValue = r.GetInt32(UnitValue);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_StockOutGoodsInfo> GetG_StockOutGoodsInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_StockOutGoodsInfo> list = new List<G_StockOutGoodsInfo>();
            totalCount = GetG_StockOutGoodsInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY StockOutGoodsId DESC) AS ORDERBYID");
            strSql.Append(",StockOutGoodsId");
            strSql.Append(",StockOutId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",Num");
            strSql.Append(",UnitValue");
            strSql.AppendFormat(" FROM G_StockOutGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockOutGoodsId = r.GetOrdinal("StockOutGoodsId");
                int StockOutId = r.GetOrdinal("StockOutId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int Num = r.GetOrdinal("Num");
                int UnitValue = r.GetOrdinal("UnitValue");
                G_StockOutGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockOutGoodsInfo();
                    model.StockOutGoodsId = r.GetInt32(StockOutGoodsId);
                    model.StockOutId = r.GetInt32(StockOutId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.Num = r.GetDecimal(Num);
                    model.UnitValue = r.GetInt32(UnitValue);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public int GetG_StockOutGoodsInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_StockOutGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_StockOutGoodsInfo GetG_StockOutGoodsInfoModel(int stockOutGoodsId)
        {
            G_StockOutGoodsInfo model = new G_StockOutGoodsInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("StockOutGoodsId");
            strSql.Append(",StockOutId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",Num");
            strSql.Append(",UnitValue");
            strSql.Append(" FROM G_StockOutGoodsInfo WITH(NOLOCK) WHERE StockOutGoodsId=@StockOutGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutGoodsId", SqlDbType.Int, 4) { Value = stockOutGoodsId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int StockOutGoodsId = r.GetOrdinal("StockOutGoodsId");
                int StockOutId = r.GetOrdinal("StockOutId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int Num = r.GetOrdinal("Num");
                int UnitValue = r.GetOrdinal("UnitValue");
                if (r.Read())
                {
                    model.StockOutGoodsId = r.GetInt32(StockOutGoodsId);
                    model.StockOutId = r.GetInt32(StockOutId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.Num = r.GetDecimal(Num);
                    model.UnitValue = r.GetInt32(UnitValue);
                }
                r.Close();
            }
            return model;
        }
    }
}
